package com.kaikeba.dao.impl;

import com.kaikeba.bean.CourierExpress;
import com.kaikeba.bean.UserExpress;
import com.kaikeba.dao.BaseCourierExpressDao;
import com.kaikeba.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CourierExpressDaoMysql implements BaseCourierExpressDao {
    private static final String SQL_CONSOLE = "SELECT COUNT(ID) DATA_SIZE,COUNT(TO_DAYS(CREATETIME)=TO_DAYS(NOW()) OR NULL) DATA_DAY FROM COURIER_EXPRESS";
    private static final String SQL_FIND_ALL = "SELECT * FROM COURIER_EXPRESS";
    private static final String SQL_FIND_BY_LIMIT = "SELECT * FROM COURIER_EXPRESS LIMIT ?,?";
    private static final String SQL_FIND_BY_PHONE = "SELECT * FROM COURIER_EXPRESS WHERE USERPHONE=?";
    private static final String SQL_INSERT = "INSERT INTO COURIER_EXPRESS(USERNAME,USERPHONE,ID_CARD,PASSWORD,CREATETIME) VALUES(?,?,?,?,NOW())";
    private static final String SQL_UPDATE_BY_ID = "UPDATE COURIER_EXPRESS SET USERNAME=?,USERPHONE=?,ID_CARD=?,PASSWORD=? WHERE ID=?";
    private static final String SQL_DELETE_BY_ID = "DELETE FROM COURIER_EXPRESS WHERE ID=?";
    private static final String SQL_FIND_BY_ID_CARD = "SELECT * FROM COURIER_EXPRESS WHERE ID_CARD=?";

    /**
     * 用于查询总的快递员人数+日注册人数
     *
     * @return 返回一个map集合{data_size:总数,data_day:日注册量}
     */
    @Override
    public Map<String, Integer> console() {
        Map<String,Integer> data = new HashMap<>();
        //获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            //执行
            result = state.executeQuery();
            if(result.next()){
                //获取查到的值
                int data_size = result.getInt("data_size");
                int data_day = result.getInt("data_day");
                //存到map集合中
                data.put("data_size",data_size);
                data.put("data_day",data_day);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //关闭资源
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    /**
     * 用于查询所有快递
     *
     * @return 返回一个装有快递员所有信息的集合
     */
    @Override
    public List<CourierExpress> findAll(boolean limit,int offset,int pageNumber) {
        //创建一个集合
        List<CourierExpress> list = new ArrayList<>();
        //连接数据库  获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        //预编译SQL语句
        try {
            if(limit) {
                state = conn.prepareStatement(SQL_FIND_BY_LIMIT);
                //填充参数
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else{
                state = conn.prepareStatement(SQL_FIND_ALL);
            }
            //填充参数   无
            //z执行
            resultSet = state.executeQuery();
            //遍历得到值
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String userPhone = resultSet.getString("userPhone");
                String id_card = resultSet.getString("ID_card");
                String password = resultSet.getString("password");
                int count = resultSet.getInt("count");
                Timestamp createtime = resultSet.getTimestamp("createtime");
                Timestamp logintime = resultSet.getTimestamp("logintime");
                //创建一个快递员对象
                CourierExpress e = new CourierExpress(id,username,userPhone,id_card,password,count,createtime,logintime);
                //存入集合中
                list.add(e);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //关闭资源
            DruidUtil.close(conn,state,resultSet);
        }
        return list;
    }

    /**
     * 通过快递员手机号查询该快递员的所有信息
     *
     * @param userPhone 快递员手机号
     * @return 返回一个快递员信息
     */
    @Override
    public CourierExpress findByPhone(String userPhone) {
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        //预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_PHONE);
            //填充参数
            state.setString(1,userPhone);
            //执行
            resultSet = state.executeQuery();
            //判断
            if(resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String id_card = resultSet.getString("ID_card");
                String password = resultSet.getString("password");
                int count = resultSet.getInt("count");
                Timestamp createtime = resultSet.getTimestamp("createtime");
                Timestamp logintime = resultSet.getTimestamp("logintime");
                //创建一个快递员对象
                CourierExpress e = new CourierExpress(id,username,userPhone,id_card,password,count,createtime,logintime);
                return e;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //关闭资源
            DruidUtil.close(conn,state,resultSet);
        }
        return null;
    }

    /**
     * 通过用户身份证号码查询用户所有信息
     *
     * @param idCard 用户身份证号码
     * @return 返回用户信息
     */
    @Override
    public CourierExpress findByIdCard(String idCard) {
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        //预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_ID_CARD);
            //填充参数
            state.setString(1,idCard);
            //执行
            resultSet = state.executeQuery();
            if(resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String userphone = resultSet.getString("userphone");
                String id_card = resultSet.getString("ID_card");
                String password = resultSet.getString("password");
                int count = resultSet.getInt("count");
                Timestamp createtime = resultSet.getTimestamp("createtime");
                Timestamp logintime = resultSet.getTimestamp("logintime");
                CourierExpress e = new CourierExpress(id,username,userphone,id_card,password,count,createtime,logintime);
                return e;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //关闭资源
            DruidUtil.close(conn,state,resultSet);
        }
        return null;
    }

    /**
     * 用于注册快递员
     *INSERT INTO COURIER_EXPRESS(USERNAME,USERPHONE,ID_CARD,PASSWORD,CREATETIME) VALUES(?,?,?,?,NOW())
     * @param e 注册快递员的信息
     * @return true表示注册成功  false表示注册失败
     */
    @Override
    public boolean insert(CourierExpress e) {
        //获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_INSERT);
            //填充参数
            state.setString(1,e.getUsername());
            state.setString(2,e.getUserPhone());
            state.setString(3,e.getIDCard());
            state.setString(4,e.getPassword());
            //执行
            int i = state.executeUpdate();
            if(i > 0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //关闭资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 通过快递员编号id修改快递员信息
     *UPDATE COURIER_EXPRESS SET USERNAME=?,USERPHONE=?,ID_CARD=?,PASSWORD=? WHERE ID=?
     * @param id                快递员编号id
     * @param newCourierExpress 新的快递员信息
     * @return true表示修改成功  false表示修改失败
     */
    @Override
    public boolean update(int id, CourierExpress newCourierExpress) {
        //获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_UPDATE_BY_ID);
            //填充参数
            state.setString(1,newCourierExpress.getUsername());
            state.setString(2,newCourierExpress.getUserPhone());
            state.setString(3,newCourierExpress.getIDCard());
            state.setString(4,newCourierExpress.getPassword());
            state.setInt(5,id);
            //执行
            int i = state.executeUpdate();
            if(i >0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //关闭资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 通过快递员id删除快递员所有信息
     *
     * @param id 快递员编号id
     * @return true表示删除成功  false表示删除失败
     */
    @Override
    public boolean delete(int id) {
        //获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_DELETE_BY_ID);
            //填充参数
            state.setInt(1,id);
            //执行
            int i = state.executeUpdate();
            if(i > 0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //关闭资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
